﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using WF_DataAccess;
using WF_Business;

namespace ExtBusiness.User
{
    /// <summary>
    /// 部门管理操作类
    /// 修改人：YZG
    /// 时间：2010-03-01
    /// </summary>
    public class DepartHandle
    {
        /// <summary>
        /// 获取最大部门号
        /// </summary>
        /// <returns></returns>
        public string GetMaxDepart()
        {
            string sql = "SELECT MAX(DepartId)+1 FROM ST_DEPARTMENT ";
            string departId = SysParams.OAConnection().GetValue(sql);

            //初始牌序号为1
            if (string.IsNullOrEmpty(departId))
            {
                departId = "1";
            }
            return departId;
        }

        /// <summary>
        /// 获取最大排序号
        /// </summary>
        /// <returns></returns>
        public string GetMaxOrderId()
        {
            string sql = "SELECT Max(to_number(ORDER_ID))+1 FROM St_Department";
            string orderId = SysParams.OAConnection().GetValue(sql);

            //初始牌序号为1
            if (string.IsNullOrEmpty(orderId))
            {
                orderId = "1";
            }

            return orderId;
        }

        /// <summary>
        /// 新增部门
        /// </summary>
        /// <param name="bmmc">部门名称</param>
        /// <param name="sjbmlist">上级部门id</param>
        /// <param name="orderId">排序号</param>
        public void AddDepartment(string bmmc, string sjbmlist, string orderId)
        {
            string sql = "";
            string departId = GetMaxDepart();

            sql = "INSERT INTO st_department(departid,depart_name, parent_id, order_id) VALUES('" + departId+ "','" + bmmc + "','" + sjbmlist + "','" + orderId + "')";
            SysParams.OAConnection().RunSql(sql);
        }

        /// <summary>
        /// 修改部门
        /// </summary>
        /// <param name="bmmc">部门名称</param>
        /// <param name="sjbm">上级部门id</param>
        /// <param name="orderId">排序号</param>
        /// <param name="departId">部门id号</param>
        public void UpdateDepartment(string bmmc, string sjbm, string orderId, string departId)
        {
            IDataAccess ida = SysParams.OAConnection(true);
            string sql = "select order_id from st_department where departid='" + departId + "'";
            string OldOrderId = SysParams.OAConnection().GetValue(sql);
            try
            {
                sql = "update st_department set depart_name='" + bmmc + "',parent_id='" + sjbm + "',order_id='"
                    + orderId + "' where departid ='" + departId + "'";
                ida.RunSql(sql);
                if (OldOrderId != orderId)  //如果产生了新orderid,则需更新其下级部门的orderid
                {
                    int pos = OldOrderId.Length + 1;
                    sql = "update st_department set order_id ='" + orderId + "'||substr(order_id," + pos.ToString() + ")"
                    + " where order_id like '" + OldOrderId + "%'";
                    ida.RunSql(sql);
                }
                ida.Close(true);
            }
            catch
            {
                ida.Close(false);
                throw;
            }
        }
        /// <summary>
        /// 删除部门
        /// </summary>
        /// <param name="bmid">部门id</param>
        /// <returns></returns>
        public int DeleteDepartment(string bmid)
        {
            string del_userdepart = "delete from st_user_department where order_id ='" + bmid + "'";
            int j = SysParams.OAConnection().RunSql(del_userdepart);
            string deldepartment = "delete from st_department where departid='" + bmid + "'";
            int i = SysParams.OAConnection().RunSql(deldepartment);
            return i;
        }
        /// <summary>
        /// 获取部门编号
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetDepartId(string id)
        {
            string sql = "SELECT parent_id FROM ST_DEPARTMENT WHERE DepartId='" + id + "'";
            return SysParams.OAConnection().GetValue(sql);
        }
        /// <summary>
        /// 获取最顶层部门
        /// </summary>
        /// <returns></returns>
        public string GetTopDepart(string sjbmlist)
        {
            string orderId = "";
            string sql = "";
            if (sjbmlist == "0")
            {
                sql = "SELECT trim(to_char(Max(to_number(ORDER_ID))+1,'000')) FROM St_Department where parent_id='0'";
                orderId = SysParams.OAConnection().GetValue(sql);
            }
            else
            {
                sql = "SELECT ORDER_ID FROM ST_DEPARTMENT WHERE DepartId='" + sjbmlist + "'";
                string parentOrderId = SysParams.OAConnection().GetValue(sql);
                sql = "select trim(to_char(Max(to_number(SUBSTR(ORDER_ID,Length('" + parentOrderId + "')+1)))+1,'000')) from st_department t Where Parent_id=" + parentOrderId;
                string tmp = SysParams.OAConnection().GetValue(sql);
                if (String.IsNullOrEmpty(tmp.Trim()))
                    orderId = parentOrderId + "001";
                else
                    orderId = parentOrderId + tmp;
            }
            return orderId;
        }
        /// <summary>
        /// 获取取部门名
        /// </summary>
        /// <returns></returns>
        public DataTable GetDepartmentName()
        {
            string sqlstring = "select depart_name from st_department where parent_id='0'";
            DataTable dt;
            SysParams.OAConnection().RunSql(sqlstring, out dt);
            return dt;
        }
        /// <summary>
        /// 获取部门信息
        /// </summary>
        /// <param name="danwei"></param>
        /// <returns></returns>
        public DataTable GetDepartment(string danwei)
        {
            string sqlstrings = "select depart_name from st_department where parent_id in(select departid from st_department where depart_name like '" + danwei + "')";
            DataTable dtbumen;
            SysParams.OAConnection().RunSql(sqlstrings, out dtbumen);
            return dtbumen;
        }
        /// <summary>
        /// 查询部门信息
        /// </summary>
        /// <param name="parent_Id"></param>
        /// <returns></returns>
        public DataTable GetDepartmentByParentId(string parent_Id)
        {
            string sqlstrings = "select departid,depart_name from st_department where parent_id='" + parent_Id + "'";
            DataTable dtbumen;
            SysParams.OAConnection().RunSql(sqlstrings, out dtbumen);
            return dtbumen;
        }

        /// <summary>
        /// 查询部门信息
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public DataTable GetDepartmentParId(string parentId)
        {
            string sqlDepartId = "select departid,depart_name from st_department where departid ='" + parentId + "'";
            DataTable dtDedepart;
            SysParams.OAConnection().RunSql(sqlDepartId, out dtDedepart);
            return dtDedepart;
        }


        /// <summary>
        /// 查询部门
        /// </summary>
        /// <returns></returns>
        public static DataTable GetDepartmentByDepartId(string depId)
        {
            //用户信息查询
            string sql = string.Format("select b.userid,b.user_name from st_user_department a,st_user b Where a.userid=b.userid And a.order_id='{0}' order by b.orderbyid",depId);

            //string sql = string.Format("select b.userid,b.user_name from st_user b Where b.deptid='{0}'",depId);
            DataTable dt;
            SysParams.OAConnection().RunSql(sql, out dt);
            return dt;
        }


        /// <summary>
        /// 重置部门
        /// </summary>
        /// <param name="orderId"></param>
        /// <param name="selecteduser"></param>
        /// <param name="sortID"></param>
        /// <param name="bmid"></param>
        public static void ResetDepartment(string orderId, string[] selecteduser, string[] sortID, string bmid)
        {
            IDataAccess dataAccess = SysParams.OAConnection(true);
            //获取form的值

            if (orderId != "")
            {
                string del_user = "delete from st_user_department where order_id = '" + bmid + "'";
                dataAccess.RunSql(del_user);
                if (selecteduser.Length > 0)
                {
                    int sortPos = 0;
                    foreach (string strUserId in selecteduser)
                    {
                        if (strUserId != "")
                        {
                            string insert_user = string.Format("insert into st_user_department (userid, order_id) values ('{0}','{1}')",
                                strUserId, bmid);
                            dataAccess.RunSql(insert_user);
                            string update_sortID = "update st_user set orderbyid='" + sortID[sortPos] + "',OPERATION_FLAG=3  where userid='" + strUserId + "'";
                            dataAccess.RunSql(update_sortID);
                        }
                        sortPos++;
                    }

                }
                dataAccess.Close(true);
            }

        }

        /// <summary>
        /// 根据用户ID查询用户所在部门ID
        /// </summary>
        /// <param name="userid">用户id</param>
        /// <returns></returns>
        public DataTable dtUserDepart(string userid)
        {
            string strSql = "select t.order_id  from st_user_department t where t.userid = '" + userid + "'";
            DataTable dt;
            SysParams.OAConnection().RunSql(strSql, out dt);
            return dt;
        }

        /// <summary>
        /// 获取非一级部门列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetDepList()
        {
            string strSql = string.Empty;
            strSql = string.Format(@"select * from st_department t where departid<>35 
                 start with t.parent_id='0'
                 connect by prior departid=parent_id");
            DataTable dtTemp;
            SysParams.OAConnection().RunSql(strSql, out dtTemp);
            return dtTemp;
        }
    }
}
